package com.example.demo.utils;

import cn.hutool.core.date.DateTime;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.poi.excel.cell.CellUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * ******************************************************
 *
 * @author liugh9
 * @version 1.0
 * @classname ReadExcelUtil
 * @description
 * @date 2023/02/14 9:55
 * <p>
 * ******************************************************
 */
public class ReadExcelUtil {

    /**
     * 读excel
     *
     * @param pathName： 文件路径；sheetNum：表索引；rowNum：行数；colNum列数；list：传递当前的字段名，不传代表从excel读取
     * @return 数组对象
     */
    public static JSONArray readExcel(String pathName, Integer sheetNum, Integer rowNum, Integer colNum, List<String> list) {
        rowNum--;//获得当前的 行和列索引
        colNum--;
        JSONArray json = new JSONArray();
        //jdk1.8的新特性
        try (
                FileInputStream fileInputStream = new FileInputStream(pathName);
        ) {
            Workbook workbook = WorkbookFactory.create(fileInputStream);
            Sheet sheet = workbook.getSheetAt(sheetNum);
            //读取总共有多少行数据
            int rowcount = sheet.getPhysicalNumberOfRows() + rowNum;
            //判断字段名是否存在，为空表示excel中 头行数据为字段名；有值表示 excel为纯数据，list中为当前字段名
            if (CollectionUtil.isEmpty(list)) {
                //获取字段名
                Row row = sheet.getRow(rowNum);
                if (row != null) {
                    //获取总共有多少列
                    int cellncount = row.getPhysicalNumberOfCells() + colNum;
                    list = new ArrayList<String>();
                    //从索引列开始读 读取数据 赋值给集合字段
                    for (int cellnum = colNum; cellnum < cellncount; cellnum++) {
                        Cell cell = row.getCell(cellnum);
                        if (cell != null) {
                            String stringCellValue = cell.getStringCellValue();
                            list.add(cellnum - colNum, stringCellValue);
                        }
                    }
                }
                rowNum++;
            }
            //从当前行索引开始读
            for (int rownum = rowNum; rownum < rowcount; rownum++) {
                JSONObject map = new JSONObject();
                Row rowData = sheet.getRow(rownum);
                if (rowData != null) {
                    //读取总列数 ，并从列索引开始 读取单元格数据 并使用 hutool工具包 CellUtil 进行类型匹配
                    int cellcount = rowData.getPhysicalNumberOfCells() + colNum;
                    for (int celnum = colNum; celnum < cellcount; celnum++) {
                        Cell cell = rowData.getCell(celnum);
                        if (cell != null) {
                            Object cellValue = CellUtil.getCellValue(cell);
                            //满足日期 进行 日期格式转换
                            if (cellValue instanceof DateTime) {
                                DateTime d = (DateTime) cellValue;
                                String s = d.toString();
                                cellValue = s;
                            }
                            map.put(list.get(celnum - colNum), cellValue);
                        }
                    }
                }
                json.add(map);
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return json;
    }

}
